using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;

namespace Helpers
{
    public class ForumSearchHelper
    {
        public ForumSearchHelper()
        {
            this.Keywords = "";
            this.KeywordSearchMode = KeywordSearch.EntirePost;
            this.ReplyCount = 0;
            this.ReplyFilterMode = ReplyFilter.AtLeast;
            this.SelectedForums.Clear();
            this.ThreadDateMode = ThreadDate.AnyDate;
            this.DateFilterMode = DateFilter.Newer;
            this.ThreadSortMode = ThreadSort.LastPostDate;
            this.User = null;
            this.UserSearchMode = UserSearch.AllPosts;
        }

        private string _keywords;
        public string Keywords
        {
            get
            {
                return _keywords;
            }
            set
            {
                if (value != null)
                {
                    if (value.Length > 100)
                    {
                        value = value.Substring(0, 100);
                    }
                }

                _keywords = value;
                _formattedKeywords = null;
            }
        }

        private string _formattedKeywords;
        public string FormattedKeywords
        {
            get
            {
                if (_formattedKeywords == null)
                {
                    _formattedKeywords = FullTextHelper.GetFullTextSearchParam(Keywords);
                }
                return _formattedKeywords;
            }
        }

        public enum KeywordSearch
        {
            EntirePost,
            SubjectsOnly
        }

        private KeywordSearch _keywordSearchMode;
        public KeywordSearch KeywordSearchMode
        {
            get
            {
                return _keywordSearchMode;
            }
            set
            {
                _keywordSearchMode = value;
            }
        }

        private DAL.User _user;
        public DAL.User User
        {
            get
            {
                return _user;
            }
            set
            {
                _user = value;
            }
        }

        public enum UserSearch
        {
            ThreadsCreated,
            AllPosts
        }

        private UserSearch _userSearchMode;
        public UserSearch UserSearchMode
        {
            get
            {
                return _userSearchMode;
            }
            set
            {
                _userSearchMode = value;
            }
        }

        private int _replyCount;
        public int ReplyCount
        {
            get
            {
                return _replyCount;
            }
            set
            {
                _replyCount = value;
            }
        }

        public enum ReplyFilter
        {
            AtLeast,
            AtMost
        }

        private ReplyFilter _replyFilterMode;
        public ReplyFilter ReplyFilterMode
        {
            get
            {
                return _replyFilterMode;
            }
            set
            {
                _replyFilterMode = value;
            }
        }

        public enum ThreadDate
        {
            AnyDate,
            OneWeekAgo,
            OneMonthAgo,
            ThreeMonthsAgo,
            SixMonthsAgo,
            OneYearAgo
        }

        private ThreadDate _threadDateMode;
        public ThreadDate ThreadDateMode
        {
            get
            {
                return _threadDateMode;
            }
            set
            {
                _threadDateMode = value;
            }
        }

        public enum DateFilter
        {
            Newer,
            Older
        }

        private DateFilter _dateFilterMode;
        public DateFilter DateFilterMode
        {
            get
            {
                return _dateFilterMode;
            }
            set
            {
                _dateFilterMode = value;
            }
        }

        public enum ThreadSort
        {
            LastPostDate,
            ThreadStartDate,
            Username,
            Forum
        }


        private ThreadSort _threadSortMode;
        public ThreadSort ThreadSortMode
        {
            get
            {
                return _threadSortMode;
            }
            set
            {
                _threadSortMode = value;
            }
        }

        public enum SortDirection
        {
            Asc,
            Desc
        }

        private SortDirection _threadSortDirection;
        public SortDirection ThreadSortDirection
        {
            get
            {
                return _threadSortDirection;
            }
            set
            {
                _threadSortDirection = value;
            }
        }

        private List<DAL.Forum> _selectedForums;
        public List<DAL.Forum> SelectedForums
        {
            get
            {
                if (_selectedForums == null)
                {
                    _selectedForums = new List<DAL.Forum>();
                }
                return _selectedForums;
            }
        }

        public enum Display
        {
            AsThreads,
            AsPosts
        }

        private Display _displayMode;
        public Display DisplayMode
        {
            get
            {
                return _displayMode;
            }
            set
            {
                _displayMode = value;
            }
        }

        public string GenerateQueryString()
        {
            List<string> myParams = new List<string>();
            if (!String.IsNullOrEmpty(FormattedKeywords))
            {
                myParams.Add(string.Format("keywords={0}", HttpContext.Current.Server.UrlEncode(FormattedKeywords)));
                myParams.Add(string.Format("keywordmode={0}", (int)this.KeywordSearchMode));
            }
            if (User != null)
            {
                myParams.Add(string.Format("user={0}", User.ID));
                myParams.Add(string.Format("userfilter={0}", (int)this.UserSearchMode));
            }
            if (ReplyCount > 0)
            {
                myParams.Add(string.Format("replies={0}", ReplyCount));
                myParams.Add(string.Format("replymode={0}", (int)ReplyFilterMode));
            }
            if (this.ThreadDateMode != ThreadDate.AnyDate)
            {
                myParams.Add(string.Format("date={0}", (int)ThreadDateMode));
                myParams.Add(string.Format("datesort={0}", (int)DateFilterMode));
            }
            foreach (DAL.Forum selectedForum in SelectedForums)
            {
                if (selectedForum != null)
                {
                    myParams.Add(string.Format("forum={0}", selectedForum.ID));
                }
            }

            myParams.Add(string.Format("sort={0}", (int)ThreadSortMode));
            myParams.Add(string.Format("sortdir={0}", (int)ThreadSortDirection));

            myParams.Add(string.Format("display={0}", (int)DisplayMode));


            string queryString = string.Join("&", myParams.ToArray());
            return queryString;
        }

        public SqlCommand GenerateSearchSqlCommand(int page)
        {
            if (page <= 1)
            {
                page = 1;
            }

            SqlCommand comm = new SqlCommand();
            StringBuilder sb = new StringBuilder();

            string searchtable = "";

            sb.AppendFormat("With temp (RowNumber, {0}) as (", GetColumnList(false)).AppendLine();
            string topClause = (page * 25).ToString();
            if (this.DisplayMode == Display.AsPosts)
            {
                searchtable = DAL.Posts.GetTableName();
                string selectClause = string.Format("Row_Number() OVER ({0}) as 'RowNumber', p.*", GetOrderByClause());
                sb.AppendLine(PostSearchSql()
                    .Replace("[selectclause]", selectClause)
                    .Replace("[topclause]", topClause));
            }
            else
            {
                searchtable = DAL.Threads.GetTableName();
                string selectClause = string.Format("Row_Number() OVER ({0}) as 'RowNumber', {1}", GetOrderByClause(), GetColumnList(true));
                sb.AppendLine(ThreadSearchSql()
                    .Replace("[selectclause]", selectClause)
                    .Replace("[topclause]", topClause));
            }

            sb.AppendLine(")");
            sb.AppendLine("SELECT * FROM temp");
            //sb.AppendFormat("INNER JOIN [{0}] x on x.ID = temp.ID", searchtable).AppendLine();

            int startIndex = ((page - 1) * 25) + 1;
            sb.AppendFormat("WHERE temp.RowNumber Between {0} and {1}", startIndex, startIndex + 24 );

            AddParams(comm);

            comm.CommandText = sb.ToString();
            return comm;
        }

        public SqlCommand GenerateSearchCountSqlCommand()
        {
            string sql = null;
            string selectClause = "Count(*)";
            if (this.DisplayMode == Display.AsPosts)
            {
                sql = PostSearchSql()
                    .Replace("[selectclause]", selectClause)
                    .Replace("TOP [topclause]", "");
            }
            else
            {
                sql = ThreadSearchSql()
                    .Replace("[selectclause]", selectClause)
                    .Replace("TOP [topclause]", "");
            }


            SqlCommand comm = new SqlCommand(sql);
            AddParams(comm);
            return comm;
        }

        private string PostSearchSql()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT TOP [topclause]");
            sb.AppendLine("[selectclause]");
            sb.AppendFormat("FROM [{0}] p", DAL.Posts.GetTableName()).AppendLine();
            sb.AppendFormat("INNER JOIN [{0}] t on t.ID = p.ThreadID", DAL.Threads.GetTableName()).AppendLine();
            if (this.User != null && this.UserSearchMode == UserSearch.ThreadsCreated)
            {
                sb.AppendFormat("INNER JOIN [{0}] p2 on p2.ID = t.FirstPostID", DAL.Posts.GetTableName()).AppendLine();
            }

            sb.AppendFormat("INNER JOIN [{0}] f on f.ID = t.ForumID", DAL.Forums.GetTableName()).AppendLine();
            if (ThreadSortMode == ThreadSort.Username)
            {
                sb.AppendFormat("INNER JOIN [{0}] u on u.ID = p.UserID", DAL.Users.GetTableName()).AppendLine();
            }


            sb.AppendLine("WHERE");
            sb.AppendLine(GetWhereClause());

            return sb.ToString();
        }

        private string ThreadSearchSql()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT [selectclause] FROM [{0}] t", DAL.Threads.GetTableName()).AppendLine();
            sb.AppendFormat("INNER JOIN [{0}] f on f.ID = t.ForumID", DAL.Forums.GetTableName()).AppendLine();
            if (ThreadSortMode == ThreadSort.Username)
            {
                sb.AppendFormat("LEFT JOIN [{0}] p2 on p2.ID = t.FirstPostID", DAL.Posts.GetTableName()).AppendLine();
                sb.AppendFormat("LEFT JOIN [{0}] u on u.ID = p2.UserID", DAL.Users.GetTableName()).AppendLine();
            }
            sb.AppendLine("WHERE EXISTS (");

            sb.AppendFormat("SELECT p.* FROM [{0}] p", DAL.Posts.GetTableName()).AppendLine();
            if (this.User != null && this.UserSearchMode == UserSearch.ThreadsCreated)
            {
                sb.AppendFormat("INNER JOIN [{0}] p2 on p2.ID = t.FirstPostID", DAL.Posts.GetTableName()).AppendLine();
            }

            //sb.AppendFormat("INNER JOIN [{0}] f on f.ID = t.ForumID", DAL.Forums.GetTableName()).AppendLine();
            sb.AppendLine("WHERE");
            sb.AppendLine(GetWhereClause());

            sb.AppendLine(")");

            return sb.ToString();
        }

        private string GetColumnList(bool includePrefixes)
        {
            char prefix;
            string colList = "";
            if (this.DisplayMode == Display.AsPosts)
            {
                prefix = 'p';
                colList = "x.ID, x.ThreadID, x.UserID, x.Subject, x.Post, x.DatePosted, x.DateModified, x.Visible";
            }
            else
            {
                prefix = 't';
                colList = "x.ID, x.ForumID, x.Priority, x.FirstPostID, x.LastPostID, x.Status, x.TotalReplies, x.TotalViews";
            }

            if (includePrefixes)
            {
                return colList.Replace('x', prefix);
            }
            else
            {
                return colList.Replace("x.", "");
            }
        }

        private void AddParams(SqlCommand comm)
        {
            if (this.User != null)
            {
                comm.Parameters.AddWithValue("@userID", this.User.ID);
            }

            DAL.User user = Managers.UserLoginManager.CurrentUser;
            if (user != null)
            {
                comm.Parameters.AddWithValue("@userStatus", (int)user.Status);
            }
            else
            {
                comm.Parameters.AddWithValue("@userStatus", 0);
            }

            comm.Parameters.AddWithValue("@keywords", FormattedKeywords);

            comm.Parameters.AddWithValue("@replies", this.ReplyCount);


            DateTime relDate = DateTime.Today;
            switch (ThreadDateMode)
            {
                case ThreadDate.OneWeekAgo:
                    relDate = DateTime.Today.AddDays(-7);
                    break;
                case ThreadDate.OneMonthAgo:
                    relDate = DateTime.Today.AddMonths(-1);
                    break;
                case ThreadDate.ThreeMonthsAgo:
                    relDate = DateTime.Today.AddMonths(-3);
                    break;
                case ThreadDate.SixMonthsAgo:
                    relDate = DateTime.Today.AddMonths(-6);
                    break;
                case ThreadDate.OneYearAgo:
                    relDate = DateTime.Today.AddYears(-1);
                    break;
            }
            comm.Parameters.AddWithValue("@relDate", relDate);

        }

        private string GetWhereClause()
        {
            List<string> whereClause = new List<string>();
            
            if (this.User != null)
            {
                switch (this.UserSearchMode)
                {
                    case UserSearch.AllPosts:
                        whereClause.Add("p.UserID = @userID");
                        break;
                    case UserSearch.ThreadsCreated:
                        whereClause.Add("p2.UserID = @userID");
                        break;
                }
            }

            if (this.DisplayMode == Display.AsThreads)
            {
                whereClause.Add("t.ID = p.ThreadID");
            }

            whereClause.Add("f.Status <= @userStatus");
            

            whereClause.Add("t.Status >= 0");
            whereClause.Add("p.Visible = 1");

            if (!String.IsNullOrEmpty(this.FormattedKeywords))
            {
                switch (this.KeywordSearchMode)
                {
                    case KeywordSearch.EntirePost:
                        whereClause.Add("Contains((p.subject, p.post), @keywords)");
                        break;
                    case KeywordSearch.SubjectsOnly:
                        whereClause.Add("Contains(p.subject, @keywords)");
                        break;
                }
            }

            if (this.ReplyCount > 0)
            {
                switch (this.ReplyFilterMode)
                {
                    case ReplyFilter.AtLeast:
                        whereClause.Add("t.TotalReplies >= @replies");
                        break;
                    case ReplyFilter.AtMost:
                        whereClause.Add("t.TotalReplies <= @replies");
                        break;
                }
            }

            if (this.SelectedForums.Count > 0)
            {
                List<string> forumIDs = new List<string>(SelectedForums.Count);
                foreach (DAL.Forum forum in SelectedForums)
                {
                    forumIDs.Add(forum.ID.ToString());
                }
                string forumIDClause = string.Join(", ", forumIDs.ToArray());
                whereClause.Add("f.ID in (" + forumIDClause + ")");
            }

            if (this.ThreadDateMode != ThreadDate.AnyDate)
            {
                string op = ">=";
                switch (this.DateFilterMode)
                {
                    case DateFilter.Newer:
                        op = ">=";
                        break;
                    case DateFilter.Older:
                        op = "<=";
                        break;
                }

                whereClause.Add(string.Format("p.DatePosted {0} @relDate", op));   
            }

            return string.Join(" AND ", whereClause.ToArray());
        }

        private string GetOrderByClause()
        {
            string sortCol = "t.LastPostID";
            string sortDir = "DESC";
        
            switch (this.ThreadSortMode)
            {
                case ThreadSort.Forum:
                    sortCol = "f.[Name]";
                    break;
                case ThreadSort.LastPostDate:
                    if (this.DisplayMode == Display.AsPosts)
                    {
                        sortCol = "p.ID";
                    }
                    else
                    {
                        sortCol = "t.[LastPostID]";
                    }
                    break;
                case ThreadSort.ThreadStartDate:
                    sortCol = "t.[FirstPostID]";
                    break;
                case ThreadSort.Username:
                    sortCol = "u.[Username]";
                    break;
            }
            switch (this.ThreadSortDirection)
            {
                case SortDirection.Asc:
                    sortDir = "ASC";
                    break;
                case SortDirection.Desc:
                    sortDir = "DESC";
                    break;
            }

            return string.Format("ORDER BY {0} {1}", sortCol, sortDir);
        }


        public static ForumSearchHelper DecodeFromQuerystring(string querystring)
        {
            if (string.IsNullOrEmpty(querystring))
            {
                return null;
            }

            
            ForumSearchHelper search = new ForumSearchHelper();
            string[] args = querystring.Split('&');
            foreach (string s in args)
            {
                string[] namevalue = s.Split('=');
                if (namevalue.Length == 2)
                {
                    string name = namevalue[0].ToLower();
                    string value = namevalue[1].ToLower();

                    int index;
                    if (!Int32.TryParse(value, out index))
                    {
                        index = 0;
                    }

                    long id;
                    if (!Int64.TryParse(value, out id))
                    {
                        id = 0;
                    }

                    switch (name)
                    {
                        case "keywords":
                            if (!String.IsNullOrEmpty(value))
                            {
                                search.Keywords = HttpContext.Current.Server.UrlDecode(value);
                            }
                            break;
                        case "keywordmode":
                            search.KeywordSearchMode = (KeywordSearch)index;
                            break;
                        case "user":
                            if (id > 0)
                            {
                                search.User = DAL.Users.Item(id);
                            }
                            break;
                        case "userfilter":
                            search.UserSearchMode = (UserSearch)index;
                            break;
                        case "replies":
                            if (index > 0)
                            {
                                search.ReplyCount = index;
                            }
                            break;
                        case "replymode":
                            search.ReplyFilterMode = (ReplyFilter)index;
                            break;
                        case "date":
                            search.ThreadDateMode = (ThreadDate)index;
                            break;
                        case "datesort":
                            search.DateFilterMode = (DateFilter)index;
                            break;
                        case "forum":
                            if (id > 0)
                            {
                                DAL.Forum forum = DAL.Forums.Item(id);
                                if (forum != null)
                                {
                                    search.SelectedForums.Add(forum);
                                }
                            }
                            break;
                        case "sort":
                            search.ThreadSortMode = (ThreadSort)index;
                            break;
                        case "sortdir":
                            search.ThreadSortDirection = (SortDirection)index;
                            break;
                        case "display":
                            search.DisplayMode = (Display)index;
                            break;
                    }

                }
            }

            return search;
        }
    }
}